{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 图表基本绘制流程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.chart import LineChart,Reference\n",
    "\n",
    "#创建一个工作簿\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['月份', '注册人数'],\n",
    "    ['1月', 866],\n",
    "    ['2月', 2335],\n",
    "    ['3月', 5710],\n",
    "    ['4月', 6482],\n",
    "    ['5月', 6120],\n",
    "    ['6月', 1605],\n",
    "    ['7月', 3813],\n",
    "    ['8月', 4428],\n",
    "    ['9月', 4631],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "#建立一个空的坐标系\n",
    "c1 = LineChart() \n",
    "\n",
    "#向空坐标系中添加数据\n",
    "data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=10)\n",
    "c1.add_data(data, titles_from_data=True)#titles_from_data=True的作用是将表头不计入数据\n",
    "\n",
    "#对图表元素进行设置\n",
    "c1.title = \"1-9月注册人数\"\n",
    "c1.style = 1 #图表样式类型(1-48)\n",
    "c1.y_axis.title = '注册人数'\n",
    "c1.x_axis.title = '月份'\n",
    "\n",
    "#将图表添加到工作簿中\n",
    "ws.add_chart(c1, \"A8\")\n",
    "\n",
    "#将工作簿进行保存\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\sample_chart.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 常用图表绘制"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 折线图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.chart import LineChart,Reference\n",
    "\n",
    "#创建一个工作簿\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['月份', '注册人数'],\n",
    "    ['1月', 866],\n",
    "    ['2月', 2335],\n",
    "    ['3月', 5710],\n",
    "    ['4月', 6482],\n",
    "    ['5月', 6120],\n",
    "    ['6月', 1605],\n",
    "    ['7月', 3813],\n",
    "    ['8月', 4428],\n",
    "    ['9月', 4631],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "#建立一个空的坐标系\n",
    "c1 = LineChart() \n",
    "\n",
    "#向空坐标系中添加数据\n",
    "data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=10)\n",
    "c1.add_data(data, titles_from_data=True)#titles_from_data=True的作用是将表头不计入数据\n",
    "\n",
    "#对图表元素进行设置\n",
    "c1.title = \"1-9月注册人数\"\n",
    "c1.style = 1 #图表样式类型(1-48)\n",
    "c1.y_axis.title = '注册人数'\n",
    "c1.x_axis.title = '月份'\n",
    "\n",
    "#将图表添加到工作簿中\n",
    "ws.add_chart(c1, \"A8\")\n",
    "\n",
    "#将工作簿进行保存\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\sample_chart.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 柱状图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.chart import BarChart,Reference\n",
    "\n",
    "#创建一个工作簿\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['月份', '注册人数'],\n",
    "    ['1月', 866],\n",
    "    ['2月', 2335],\n",
    "    ['3月', 5710],\n",
    "    ['4月', 6482],\n",
    "    ['5月', 6120],\n",
    "    ['6月', 1605],\n",
    "    ['7月', 3813],\n",
    "    ['8月', 4428],\n",
    "    ['9月', 4631],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "#建立一个空的坐标系\n",
    "c1 = BarChart() \n",
    "\n",
    "#向空坐标系中添加数据\n",
    "data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=10)\n",
    "c1.add_data(data, titles_from_data=True)#titles_from_data=True的作用是将表头不计入数据\n",
    "\n",
    "#对图表元素进行设置\n",
    "c1.title = \"1-9月注册人数\"\n",
    "c1.style = 1 #图表样式类型(1-48)\n",
    "c1.y_axis.title = '注册人数'\n",
    "c1.x_axis.title = '月份'\n",
    "\n",
    "#将图表添加到工作簿中\n",
    "ws.add_chart(c1, \"A8\")\n",
    "\n",
    "#将工作簿进行保存\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\bar_chart.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 面积图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.chart import AreaChart,Reference\n",
    "\n",
    "#创建一个工作簿\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['月份', '注册人数'],\n",
    "    ['1月', 866],\n",
    "    ['2月', 2335],\n",
    "    ['3月', 5710],\n",
    "    ['4月', 6482],\n",
    "    ['5月', 6120],\n",
    "    ['6月', 1605],\n",
    "    ['7月', 3813],\n",
    "    ['8月', 4428],\n",
    "    ['9月', 4631],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "#建立一个空的坐标系\n",
    "c1 = AreaChart() \n",
    "\n",
    "#向空坐标系中添加数据\n",
    "data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=10)\n",
    "c1.add_data(data, titles_from_data=True)#titles_from_data=True的作用是将表头不计入数据\n",
    "\n",
    "#对图表元素进行设置\n",
    "c1.title = \"1-9月注册人数\"\n",
    "c1.style = 13 #图表样式类型(1-48)\n",
    "c1.y_axis.title = '注册人数'\n",
    "c1.x_axis.title = '月份'\n",
    "\n",
    "#将图表添加到工作簿中\n",
    "ws.add_chart(c1, \"A8\")\n",
    "\n",
    "#将工作簿进行保存\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\area_chart.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 散点图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.chart import ScatterChart,Reference,Series\n",
    "\n",
    "#创建一个工作簿\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['月份', '注册人数',''],\n",
    "    ['1月', 866],\n",
    "    ['2月', 2335],\n",
    "    ['3月', 5710],\n",
    "    ['4月', 6482],\n",
    "    ['5月', 6120],\n",
    "    ['6月', 1605],\n",
    "    ['7月', 3813],\n",
    "    ['8月', 4428],\n",
    "    ['9月', 4631],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "#建立一个空的坐标系\n",
    "c1 = ScatterChart() \n",
    "\n",
    "#向空坐标系中添加数据\n",
    "xvalues = Reference(ws, min_col=1, min_row=1, max_row=10)\n",
    "values = Reference(ws, min_col=2, min_row=1, max_row=10)\n",
    "series = Series(values, xvalues, title_from_data=True)\n",
    "c1.series.append(series)\n",
    "\n",
    "#设置点的样式\n",
    "s1 = c1.series[0]\n",
    "s1.marker.symbol = \"circle\"#设置散点标记类型\n",
    "s1.marker.graphicalProperties.solidFill = \"FF0000\" # 标记填充颜色\n",
    "s1.marker.graphicalProperties.line.solidFill = \"FF0000\" # 标记的轮廓颜色\n",
    "\n",
    "s1.graphicalProperties.line.noFill = True#等于True时，点之间的线才会隐藏\n",
    "\n",
    "#对图表元素进行设置\n",
    "c1.title = \"1-9月注册人数\"\n",
    "c1.style = 13 #图表样式类型(1-48)\n",
    "c1.y_axis.title = '注册人数'\n",
    "c1.x_axis.title = '月份'\n",
    "\n",
    "#将图表添加到工作簿中\n",
    "ws.add_chart(c1, \"A8\")\n",
    "\n",
    "#将工作簿进行保存\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\scatter_chart.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 气泡图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.chart import BubbleChart,Reference,Series\n",
    "\n",
    "#创建一个工作簿\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['月份', '注册人数','区间'],\n",
    "    ['1月', 866, 10],\n",
    "    ['2月', 2335, 20],\n",
    "    ['3月', 5710, 50],\n",
    "    ['4月', 6482, 60],\n",
    "    ['5月', 6120, 60],\n",
    "    ['6月', 1605, 10],\n",
    "    ['7月', 3813, 30],\n",
    "    ['8月', 4428, 40],\n",
    "    ['9月', 4631, 40],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "#建立一个空的坐标系\n",
    "c1 = BubbleChart() \n",
    "\n",
    "#向空坐标系中添加数据\n",
    "xvalues = Reference(ws, min_col=1, min_row=1, max_row=10)#x值\n",
    "yvalues = Reference(ws, min_col=2, min_row=2, max_row=10)#y值\n",
    "size = Reference(ws, min_col=3, min_row=1, max_row=10)#size值\n",
    "\n",
    "series = Series(values=yvalues, xvalues=xvalues, zvalues=size)\n",
    "c1.series.append(series)\n",
    "\n",
    "\n",
    "#对图表元素进行设置\n",
    "c1.title = \"1-9月注册人数\"\n",
    "c1.style = 13 #图表样式类型(1-48)\n",
    "c1.y_axis.title = '注册人数'\n",
    "c1.x_axis.title = '月份'\n",
    "\n",
    "#将图表添加到工作簿中\n",
    "ws.add_chart(c1, \"A8\")\n",
    "\n",
    "#将工作簿进行保存\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\bubble_chart.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 图表布局"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.chart.layout import Layout, ManualLayout\n",
    "from openpyxl.chart.legend import Legend\n",
    "wb = Workbook()#建立工作表\n",
    "ws = wb.active#激活工作表\n",
    "for i in range(10):\n",
    "     ws.append([i])\n",
    "\n",
    "from openpyxl.chart import BarChart, Reference, Series\n",
    "values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)#数据引用\n",
    "chart = BarChart()#建立坐标系\n",
    "chart.add_data(values)#添加数据\n",
    "ws.add_chart(chart, \"B2\")#添加图表\n",
    "\n",
    "from copy import deepcopy\n",
    "chart1 = deepcopy(chart)\n",
    "chart1.layout = Layout(ManualLayout(x = 0.2,y = 0.1,h=0.6,w=0.8))\n",
    "chart1.legend = Legend(legendPos='b')\n",
    "ws.add_chart(chart1, \"B17\")#添加图表\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\Layout_Chart.xlsx')#保存工作表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 将图片插入到Excel中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.drawing.image import Image\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "# 指明要导入图片的文件路径\n",
    "img = Image(r'C:\\Users\\zhangjunhong\\Desktop\\图片 1.png')\n",
    "\n",
    "#将图片添加到 ws 中\n",
    "ws.add_image(img, 'A1')\n",
    "\n",
    "#对图片的宽和高进行设置\n",
    "newsize = (90, 90)\n",
    "img.width, img.height = newsize\n",
    "\n",
    "#保存工作簿\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\logo.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
